import os.path

import numpy as np
import pandas as pd
import datetime as datetime

import warnings
import openpyxl
from openpyxl import load_workbook
from dateutil.relativedelta import relativedelta

'''在职人员筛选'''


def readOnJob():
    在职人员名单文件 = '../文件/在职人员报表2024-01-23.xlsx'
    在保人员清单文件 = '../文件/裕心在保人员清单.xlsx'
    基础配置文件路径 = '../文件/基础信息配置文件.xlsx'
    特殊人员清单文件 = '../文件/裕心特殊人员清单（一般不需修改）.xlsx'
    # excel读取时的忽略警告信息
    warnings.filterwarnings('ignore')
    excelDate = pd.read_excel(在职人员名单文件, dtype=str)
    # 读取基础信息配置表
    messageDate = pd.read_excel(基础配置文件路径, sheet_name='缴裕心险的BU', dtype=str)
    messageList = messageDate.iloc[:, 0].tolist()
    # print(messageList)
    print(messageList)
    print(excelDate)
    # 读取特殊人员清单文件
    specialExcelDate = pd.read_excel(特殊人员清单文件, sheet_name='特殊购买的人员', dtype=str)
    notspecialExcelDate = pd.read_excel(特殊人员清单文件, sheet_name='特殊不购买的人员', dtype=str)
    notspecialExcelDateList = notspecialExcelDate.iloc[:, 1].tolist()
    print(notspecialExcelDateList)
    # 职层为课长层以上的，并且 人员类别为劳动合同工或裕备生的
    # date = excelDate[
    #     (excelDate['职层'] == '课长层') | (excelDate['职层'] == '经理层') | (excelDate['职层'] == '总经理层') | (excelDate[
    #         '职层'] == '总监层') | (excelDate['职层'] == '副总裁层') | (excelDate['职层'] == '总裁层')]
    # 过滤条件
    excelDate = excelDate[excelDate['所属公司'].isin(messageList)]
    filterate = ['课长层', '经理层', '总经理层', '总监层', '副总裁层', '总裁层']
    date = excelDate[excelDate['职层'].isin(filterate)]
    # datePlus=date[(date['人员类别']=='劳动合同工') | (date['人员类别']=='裕备生')]
    datePlus = date[date['人员类别'].isin(['劳动合同工', '裕备生', '退休返聘'])]  # 课长层以上的岗位不包含‘实习生’。‘顾问’
    print(datePlus)
    # 职层为课长层以下的， 人员类别为：劳动合同工或裕备生、退休返聘，岗位为：实习生、顾问

    current_time = datetime.datetime.now()
    one_year_ago = current_time - datetime.timedelta(days=365)
    time = one_year_ago.strftime("%Y-%m-%d")
    print(f'查找入职时间小于: {time}的数据')
    dates = excelDate[~excelDate['职层'].isin(filterate)]
    oneyear = dates[dates['入职日期'] <= time]
    # 属于劳动合同工、裕备生的并且入职满一年的
    oneyeardate1 = oneyear[oneyear['人员类别'].isin(['劳动合同工', '裕备生'])]
    # 人员类别 属于'退休返聘'人员的都购买
    oneyeardate2 = dates[dates['人员类别'].isin(['退休返聘'])]
    # 岗位 属于'顾问','实习生'人员的都购买
    oneyeardate3 = dates[dates['岗位'].isin(['顾问', '实习生'])]

    oneyeardateAll = pd.concat([oneyeardate1, oneyeardate2, oneyeardate3], axis=0)
    oneyeardate = oneyeardateAll.drop_duplicates()  # 去除重复行

    print(oneyeardate)
    # 整合两个dateFrame
    personAllDate = pd.concat([datePlus, oneyeardate], axis=0)

    print(personAllDate)

    # 过滤掉特殊不需要购买裕心险的人员
    personDate = personAllDate[~personAllDate['证件号码'].isin(notspecialExcelDateList)]
    print(f'过滤特殊不购买后的{personDate}')
    # 特殊人员清单与在职人员表进行匹配，比对出特殊需要购买的人员名单
    specialExcelDate.rename(columns={'身份证号': '证件号码'}, inplace=True)
    mergeSpecialDate = pd.merge(excelDate, specialExcelDate['证件号码'], on='证件号码', how='inner')
    print(mergeSpecialDate)
    # 将整理的特殊人员数据，与过滤规则过滤的数据进行合并
    personDate = pd.concat([personDate, mergeSpecialDate], axis=0).drop_duplicates()

    # 读取再保人员名单
    inPlayDate = pd.read_excel(在保人员清单文件)
    # 将再保人员清单的'证件号'列重命名为'证件号码'
    inPlayDate.rename(columns={'证件号': '证件号码'}, inplace=True)

    # 合并两个dateframe进行筛选,采用左连的方式
    mergeDate = pd.merge(personDate, inPlayDate['证件号码'], on='证件号码', how='left', indicator=True)
    # 将仅存在personDate的数据过滤出来
    leftMergeDate = mergeDate[mergeDate._merge == 'left_only']
    # 保存到文件中
    nowday = current_time.strftime('%Y-%m-%d')
    过滤后文件路径 = f'../文件/increase_Date{nowday}.xlsx'
    leftMergeDate.to_excel(过滤后文件路径, index=None)


# readOnJob()


def inserDate():
    过滤后文件路径 = '../文件/筛选在职人员2024-04-23.xlsx'
    基础配置文件路径 = '../文件/基础信息配置文件.xlsx'
    保全模板路径 = '../文件/申请保全模板.xlsx'
    readDate = pd.read_excel(过滤后文件路径, dtype='str')
    缴交BU = pd.read_excel(基础配置文件路径, sheet_name='缴裕心险的BU', dtype=str)
    messageDate = pd.read_excel(基础配置文件路径, sheet_name='海外工作地点', dtype=str)
    print(messageDate)
    messageList = messageDate.iloc[:, 0].tolist()

    print(messageList)
    在职人员列表 = []
    for index, row in readDate.iterrows():
        # dateList = ['', '', '', '', '', '', '', '', '', '', '', '', ]
        dateList = [''] * 44
        # 工号为空时就退出
        if str(row['工号']) == 'nan':
            break
        # 序号
        dateList[0] = str(index + 1)
        # 保障方案
        if str(row['工作地点']) in messageList:
            dateList[1] = '海外员工方案'
        else:
            dateList[1] = '员工方案'
        # 被保险人姓名
        dateList[2] = row['姓名']
        # 证件类型
        dateList[3] = row['证件类型']
        # 证件号
        dateList[4] = str(row['证件号码'])
        # 性别
        if row['性别'] == '男':
            dateList[7] = 'M'
        else:
            dateList[7] = 'F'
        # 出生日期
        dateList[8] = str(row['出生日期']).replace('-', '')
        # 生效日期
        filterate = ['课长层', '经理层', '总经理层', '总监层', '副总裁层', '总裁层']
        gangwei = ['实习生', '顾问']

        if ((row['职层'] in filterate) or (row['岗位'] in gangwei) or (row['人员类别'] in ['退休返聘'])):
            day = row['入职日期']
        else:
            date_str = row['入职日期']
            # 将字符串转换为datetime对象
            date_object = datetime.datetime.strptime(date_str, "%Y-%m-%d")
            # 在当前日期上加一年
            new_date = date_object + relativedelta(years=1)
            # new_date = date_obj + datetime.timedelta(days=365)
            # 将datetime对象转换回字符串
            new_date_str = new_date.strftime("%Y-%m-%d")
            print(new_date_str)
            day = new_date_str
        day = day.replace('-', '')
        dateList[9] = day
        # 居住地
        dateList[10] = row['工作地点']
        # 投保原因
        dateList[11] = '新入职'
        # 保险人性质
        dateList[12] = '新保'
        dateList[13] = row['所属公司']
        # 1.联系电话：根据在职人员报表中电话
        dateList[14] = row['手机']
        # 2.人员关系默认：员工（本人）
        dateList[16] = '员工（本人）'
        # 3.开票单位默认：所属BU对应的公司全称
        inJob = 缴交BU[缴交BU['在缴BU'].isin([row['所属公司']])]
        dateList[20] = inJob['关联机构全称'].tolist()[0]
        # 4.社保所在地：在职人员表中的工作地点
        dateList[23] = str(row['工作地点']).split('-')[0]
        # dateList[23] = inJob['授权人办公地址'].tolist()[0]
        # 5.职业类别：默认三类
        dateList[37] = '三类'
        # 6。追溯期：默认正常
        # dateList[43] = '正常'
        在职人员列表.append(dateList)
    print(在职人员列表)
    wb = openpyxl.load_workbook(保全模板路径)
    sheet = wb['增加被保险人']
    start_row = 10
    for item in 在职人员列表:
        for col in range(1, len(item) + 1):
            sheet.cell(row=start_row, column=col).value = item[col - 1]
        # 约定追溯期根据单元格公式来填写
        sheet.cell(row=start_row,
                   column=44).value = f'=IF(TODAY()-CONCATENATE(LEFT(J{start_row},4),"/",MID(J{start_row},5,2),"/",RIGHT(J{start_row},2))>=$AR$8,"超追溯","正常")'
        start_row += 1
    wb.save(保全模板路径)


# inserDate()

# Message = '本次流程运行共选择{}个BU的的人员,下载在职人员数据:{}条数据,属于课长层以上的{}条,属于员工层的{}条,属于特殊人员的有{}条,下载离职人员数据{}条数据,比对裕心在保名单{}条数据,得出新增被保人员{}条,得出减少被保人员{}条'.format(
#     len(messageList), excelDate.shape[0], datePlus.shape[0], oneyeardate.shape[0], mergeSpecialDate.shape[0],
#     inCompanyDate.shape[0], inPlayDate.shape[0], leftMergeDate.shape[0], mergeDate.shape[0])
# inserDate()

# current_time = datetime.datetime.now()
# one_year_ago = current_time - datetime.timedelta(days=365)
# time = one_year_ago.strftime("%Y-%m-%d")
# print(time)

'''离职人员筛选'''


def readNotOnJob():
    warnings.filterwarnings('ignore')
    离职人员名单文件 = '../文件/离职人员报表2024-01-25.xlsx'
    基础配置文件路径 = '../文件/基础信息配置文件.xlsx'
    在保人员清单文件 = '../文件/裕心在保人员清单.xlsx'

    exceldate = pd.read_excel(离职人员名单文件, dtype='str')

    BUdate = pd.read_excel(基础配置文件路径, sheet_name='缴裕心险的BU', dtype='str')
    BUList = BUdate.iloc[:, 0].tolist()
    BUList.append('NaN')
    print(f'当前在缴的BU是:{BUList}')
    inCompanyDate = exceldate[(exceldate['所属公司'].isin(BUList)) | (exceldate['所属公司'].isna())]
    print(f'筛选出属于当前BU的,共有{inCompanyDate.shape[0]}条数据')

    inPlayDate = pd.read_excel(在保人员清单文件)
    # 将再保人员清单的'证件号'列重命名为'证件号码'
    inPlayDate.rename(columns={'证件号': '证件号码'}, inplace=True)
    # 两个dateframe进行筛选,两者都存在的数据
    mergeDate = pd.merge(inCompanyDate, inPlayDate['证件号码'], on='证件号码', how='inner')
    print(f'处于在缴名单中,但已经离职的共有{mergeDate.shape[0]}条数据')
    day = datetime.datetime.now().strftime('%Y-%m-%d')
    过滤后离职人员文件路径 = f'../文件/reduce_Date{day}.xlsx'
    mergeDate.to_excel(过滤后离职人员文件路径, index=None)


# readNotOnJob()


def insertReduceDate():
    过滤后离职人员文件路径 = f'../文件/reduce_Date2024-01-25.xlsx'
    保全模板路径 = '../文件/申请保全模板.xlsx'
    exceldate = pd.read_excel(过滤后离职人员文件路径, dtype='str')
    离职减员列表 = []
    for index, row in exceldate.iterrows():
        dateList = ['', '', '', '', '', '', '', '', '', '', '', '']
        dateList[0] = str(index + 1)
        dateList[1] = row['姓名']
        dateList[2] = row['证件类型']
        dateList[3] = row['证件号码']
        # dateList[4] = datetime.datetime.now().strftime('%Y%m%d')

        dateList[4] = row['离职日期'].replace('-', '')
        dateList[5] = row['所属公司']
        # dateList[10] = '超追溯'
        dateList[11] = '离职'
        离职减员列表.append(dateList)
    print(离职减员列表)
    wb = openpyxl.load_workbook(保全模板路径)
    sheet = wb['减少被保险人']
    start_row = 10
    for item in 离职减员列表:
        for col in range(1, len(item) + 1):
            sheet.cell(row=start_row, column=col).value = item[col - 1]
        # 约定追溯期根据单元格公式来填写=IF(TODAY()-CONCATENATE(LEFT(E10,4),"/",MID(E10,5,2),"/",RIGHT(E10,2))>=$J$8,"超追溯","正常")
        sheet.cell(row=start_row,
                   column=11).value = f'=IF(TODAY()-CONCATENATE(LEFT(E{start_row},4),"/",MID(E{start_row},5,2),"/",RIGHT(E{start_row},2))>=$K$8,"超追溯","正常")'
        start_row += 1
    wb.save(保全模板路径)


# insertReduceDate()

def readMessage():
    warnings.filterwarnings('ignore')
    基础配置文件路径 = '../文件/基础信息配置文件.xlsx'
    messageDate = pd.read_excel(基础配置文件路径, sheet_name='非跨BU异动', header=1)
    fillDate = messageDate.fillna(method='ffill')
    非跨BU = {}
    print(fillDate)
    for index, row in fillDate.iterrows():
        kes = 非跨BU.keys()
        if (row['地区'] not in (kes)):
            date = []
            date.append(row['所属公司'])
            非跨BU[row['地区']] = date
        else:
            非跨BU[row['地区']].append(row['所属公司'])
    return 非跨BU


# date=readMessage()
# print(date)

# current_date = datetime.datetime.now()
# previous_month = current_date - datetime.timedelta(days=current_date.day)
# previous_month_str = previous_month.strftime('%Y-%m')
# day = previous_month_str + '-26'
# print(f'过滤异动人员时间范围大于{day}')


def changes():
    warnings.filterwarnings('ignore')
    # 读取基础信息配置文件中非跨BU的sheet页中的数据并返还map形式的数据
    非跨BU = readMessage()
    异动人员名单文件 = '../文件/下载异动人员报表2024-04-26.xlsx'
    本地运行文件夹路径 = '../文件'
    从上上个月几号开始过滤数据 = '01'
    pd.set_option('display.max_columns', None)
    changeExcelDate = pd.read_excel(异动人员名单文件, header=[1, 2])

    heardList = changeExcelDate.columns.tolist()
    print(heardList)
    if ('Unnamed' in heardList[0]):
        heardmap = {'Unnamed: 0': '工号', 'Unnamed: 1': '姓名', 'Unnamed: 2': '异动类型', 'Unnamed: 3': '异动原因',
                    'Unnamed: 4': '入职日期',
                    'Unnamed: 5': '生效日期'}
        changeExcelDate.rename(columns=heardmap, inplace=True)

    print(changeExcelDate)
    print(f'本次下载异动人员{changeExcelDate.shape[0]}条数据')
    # 过滤出大于上上个月26号到当前日期的数据
    current_date = datetime.datetime.now()
    previous_month = current_date - relativedelta(months=2)
    # previous_month = current_date - datetime.timedelta(days=current_date.day)
    previous_month_str = previous_month.strftime('%Y-%m')
    day = previous_month_str + '-' + str(从上上个月几号开始过滤数据)
    print(f'过滤异动人员时间范围大于{day}')
    date = changeExcelDate[(changeExcelDate['生效日期'] >= day) & ~(changeExcelDate['工号'] == 'Employee number')]
    print(f'过滤异动人员时间范围大于{day},的有{date.shape[0]}条数据')
    changeList = []
    for index, row in date.iterrows():  # 遍历每一行数据
        isInBU = True
        for key, value in 非跨BU.items():  # 判断是否符合过滤条件
            if ((row['所属公司'] in value) and (row['所属公司.1'] in value)):
                # print('属于同一BU的',row.tolist())
                isInBU = False
                break
        if (isInBU == True):
            # print('不属于同一BU的,添加', row.tolist())
            changeList.append(row.tolist())

    changeBU = pd.DataFrame(changeList, columns=changeExcelDate.columns)
    print(f'过滤非同一地区的BU后有{changeBU.shape[0]}条数据')
    day = datetime.datetime.now().strftime('%Y-%m-%d')
    过滤后异动人员文件路径 = f'{本地运行文件夹路径}/筛选本月异动人员{day}.xlsx'
    changeBU.to_excel(过滤后异动人员文件路径, index=None)


# changes()

# def readex():
#     在保人员清单文件 = '../文件/深圳市裕同包装科技股份有限公司在保人员清单（截至20240417）.xls'
#     # df = pd.read_excel(在保人员清单文件, engine='openpyxl', options={'password': 'YUTO'})
#     df = pd.read_excel(在保人员清单文件, engine='openpyxl',  engine_kwargs={'password': 'YUTO'})
#     print(df)
#
# readex()

def insertChangedate():
    pd.set_option('display.max_columns', None)
    过滤后异动人员文件路径 = '../文件/筛选异动人员2024-01-31.xlsx'
    在职人员名单文件 = '../文件/下载在职人员报表2024-03-06.xlsx'
    在保人员清单文件 = '../文件/裕心在保人员清单.xlsx'
    基础配置文件路径 = '../文件/基础信息配置文件.xlsx'
    保全模板路径 = '../文件/申请保全模板.xlsx'
    本地运行文件夹路径 = '../文件'
    filterate = ['课长层', '经理层', '总经理层', '总监层', '副总裁层', '总裁层']
    type = ['劳动合同工', '裕备生', '退休返聘']

    # filterate = ['课长层', '经理层', '总经理层', '总监层', '副总裁层', '总裁层']
    # type = ['劳动合同工', '裕备生', '退休返聘']

    changeexcel = pd.read_excel(过滤后异动人员文件路径, dtype='str')
    changeexcel.rename(columns={'所属公司': '异动后所属公司', '所属公司.1': '异动前所属公司'}, inplace=True)
    print(changeexcel)
    inJobExcel = pd.read_excel(在职人员名单文件, dtype='str')
    isBuyExcel = pd.read_excel(在保人员清单文件, dtype='str')

    缴交BU = pd.read_excel(基础配置文件路径, sheet_name='缴裕心险的BU', dtype='str')
    # 根据工号匹配出在职人员名单中的数据
    mergedate = pd.merge(inJobExcel, changeexcel[['工号', '生效日期', '异动后所属公司', '异动前所属公司']], on='工号',
                         how='inner', indicator=True)

    print(f'异动人员匹配在职表后有{mergedate.shape[0]}条数据')
    # 筛选符合大规则的数据
    messageLocList = 缴交BU.iloc[:, 0].tolist()
    excelDate = mergedate[mergedate['所属公司'].isin(messageLocList)]
    print(f'本次筛选所属公司属于{messageLocList}，的BU的人员')

    # 课长层以上的，并且人员类型为劳动合同工或裕备生的
    dates1 = excelDate[excelDate['职层'].isin(filterate)]
    datePlus1 = dates1[dates1['人员类别'].isin(type)]
    # 职层为课长层以下的，并且入职时间满一年的，并且 人员类别为：劳动合同工或裕备生的
    current_time = datetime.datetime.now()

    one_year_ago = current_time - datetime.timedelta(days=365)
    time = one_year_ago.strftime("%Y-%m-%d")
    dates2 = excelDate[~excelDate['职层'].isin(filterate)]
    oneyear = dates2[dates2['入职日期'] <= time]
    # datePlus2 = oneyear[oneyear['人员类别'].isin(['劳动合同工', '裕备生'])]
    # 属于劳动合同工、裕备生的并且入职满一年的
    datePlusdate1 = oneyear[oneyear['人员类别'].isin(['劳动合同工', '裕备生'])]
    # 人员类别 属于'退休返聘'人员的都购买
    datePlusdate2 = dates2[dates2['人员类别'].isin(['退休返聘'])]
    # 岗位 属于'顾问','实习生'人员的都购买
    datePlusdate3 = dates2[dates2['岗位'].isin(['顾问', '实习生'])]

    datePusdateAll = pd.concat([datePlusdate1, datePlusdate2, datePlusdate3], axis=0)
    datePlus2 = datePusdateAll.drop_duplicates()  # 去除重复行

    # 整合两个dateFrame，高管层和普通层的
    lastDate = pd.concat([datePlus1, datePlus2], axis=0)
    print(f'异动人员中符合大规则的有{lastDate.shape[0]}条数据')

    # 将再保人员清单的'证件号'列重命名为'证件号码'
    isBuyExcel.rename(columns={'证件号': '证件号码'}, inplace=True)
    # 整合后的，与再保人员进行匹配,两者都存在的数据
    mergeDate = pd.merge(lastDate, isBuyExcel['证件号码'], on='证件号码', how='inner')
    print(f'异动人员中符合大规则的且与在保人员清单匹配后有{mergeDate.shape[0]}条数据')
    print(mergeDate)
    # 将于在保人员表比对后的数据保存到Excel中
    day = datetime.datetime.now().strftime('%Y-%m-%d')
    与在保数据筛选后的异动人员 = f'{本地运行文件夹路径}/与在保数据筛选后的异动人员{day}.xlsx'
    mergeDate.to_excel(与在保数据筛选后的异动人员, index=None)

    # 获取保全模板中增加被保险人和减少被保险人sheet页的行数
    insertExceladd = pd.read_excel(保全模板路径, sheet_name='增加被保险人', dtype='str')
    insertExceldel = pd.read_excel(保全模板路径, sheet_name='减少被保险人', dtype='str')

    rowNumberadd = insertExceladd.shape[0] + 1
    rowNumberdel = insertExceldel.shape[0] + 1
    print(f'增加被保险人sheet页中原来有:{rowNumberadd}行数据')
    print(f'减少被保险人sheet页中原来有:{rowNumberdel}行数据')

    # 模板增加‘减少被保险人’sheet页数据
    离职减员列表 = []
    for index, row in mergeDate.iterrows():
        dateList = ['', '', '', '', '', '', '', '', '', '', '', '']
        dateList[0] = str(index + rowNumberdel)
        dateList[1] = row['姓名']
        dateList[2] = row['证件类型']
        dateList[3] = row['证件号码']
        # dateList[4] = datetime.datetime.now().strftime('%Y%m%d')
        dateList[4] = row['生效日期'].replace('-', '')
        # dateList[10] = '超追溯'
        # 根据工号查找到异动后的所属公司
        numberList = []
        number = row['工号']
        numberList.append(number)
        numberRow = changeexcel[changeexcel['工号'].isin(numberList)]
        # dateList[5] = numberRow['所属公司'].item()
        dateList[5] = numberRow['异动前所属公司'].tolist()[0]

        dateList[11] = '从' + row['异动前所属公司'] + '异动到' + row['异动后所属公司']
        离职减员列表.append(dateList)
    print(f'将要写入到减少被保险人sheet页中的有:{len(离职减员列表)}行数据')
    print(离职减员列表)
    # 用openyxl写入Excel中对应sheet页的数据
    wb = openpyxl.load_workbook(保全模板路径)
    sheet = wb['减少被保险人']
    start_row = rowNumberdel + 1
    for item in 离职减员列表:
        for col in range(1, len(item) + 1):
            sheet.cell(row=start_row, column=col).value = item[col - 1]
        sheet.cell(row=start_row,
                   column=11).value = f'=IF(TODAY()-CONCATENATE(LEFT(E{start_row},4),"/",MID(E{start_row},5,2),"/",RIGHT(E{start_row},2))>=$K$8,"超追溯","正常")'
        start_row += 1
    # 写入完成，暂时不保存

    # 模板增加‘减少被保险人’sheet页数据
    messageDate = pd.read_excel(基础配置文件路径, sheet_name='海外工作地点', dtype=str)
    messageList = messageDate.iloc[:, 0].tolist()
    print(f'所属公司包含{messageList}，的是属于海外员工方案的')
    在职人员列表 = []
    for index, row in mergeDate.iterrows():
        # dateList = ['', '', '', '', '', '', '', '', '', '', '', '', '', '']
        dateList = [''] * 44
        # 工号为空时就退出
        if str(row['工号']) == 'nan':
            break
        # 序号
        dateList[0] = str(index + rowNumberadd)
        # 保障方案
        if str(row['工作地点']) in messageList:
            dateList[1] = '海外员工方案'
        else:
            dateList[1] = '员工方案'
        # 被保险人姓名
        dateList[2] = row['姓名']
        # 证件类型
        dateList[3] = row['证件类型']
        # 证件号
        dateList[4] = str(row['证件号码'])
        # 性别
        if row['性别'] == '男':
            dateList[7] = 'M'
        else:
            dateList[7] = 'F'
        # 出生日期
        dateList[8] = row['出生日期'].replace('-', '')
        # 生效日期
        # day = datetime.datetime.now().strftime('%Y%m%d')
        day = row['生效日期'].replace('-', '')

        dateList[9] = day
        # 获取新居住地，根据工号去匹配，因为在异动人员表中不存在证件号码
        numberList = []
        number = row['工号']
        numberList.append(number)
        print(numberList)
        numberRow = changeexcel[changeexcel['工号'].isin(numberList)]
        # dateList[10] = numberRow['工作地点'].item()
        dateList[10] = numberRow[['工作地点']].iloc[0, 0]
        # 投保原因
        dateList[11] = '内部异动'
        # 保险人性质
        dateList[12] = '重新投保'
        dateList[13] = row['所属公司']
        # 1.联系电话：根据在职人员报表中电话
        dateList[14] = row['手机']
        # 2.人员关系默认：员工（本人）
        dateList[16] = '员工（本人）'
        # 3.开票单位默认：所属BU对应的公司全称
        inJob = 缴交BU[缴交BU['在缴BU'].isin([row['所属公司']])]
        dateList[20] = inJob['关联机构全称'].tolist()[0]
        # 4.社保所在地：在职人员表中的工作地点
        dateList[23] = str(row['工作地点']).split('-')[0]
        # dateList[23] = inJob['授权人办公地址'].tolist()[0]
        # 5.职业类别：默认三类
        dateList[37] = '三类'
        # 6。追溯期：默认正常
        # dateList[43] = '正常'
        在职人员列表.append(dateList)
    print(f'将要写入到增加被保险人sheet页中的有:{len(离职减员列表)}行数据')
    # wb = openpyxl.load_workbook(保全模板路径)
    print(在职人员列表)
    sheet = wb['增加被保险人']
    start_row = rowNumberadd + 1
    for item in 在职人员列表:
        for col in range(1, len(item) + 1):
            sheet.cell(row=start_row, column=col).value = item[col - 1]
        sheet.cell(row=start_row,
                   column=44).value = f'=IF(TODAY()-CONCATENATE(LEFT(J{start_row},4),"/",MID(J{start_row},5,2),"/",RIGHT(J{start_row},2))>=$AR$8,"超追溯","正常")'
        start_row += 1

    wb.save(保全模板路径)


# insertChangedate()
'''
    changeexcel = pd.read_excel(过滤后异动人员文件路径, dtype='str')
    inJobExcel = pd.read_excel(在职人员名单文件, dtype='str')
    isBuyExcel = pd.read_excel(在保人员清单文件, dtype='str')
    messageDate = pd.read_excel(基础配置文件路径, sheet_name='缴裕心险的BU', dtype='str')
    # 根据工号匹配出在职人员名单中的数据
    mergedate = pd.merge(inJobExcel, changeexcel['工号'], on='工号', how='inner', indicator=True)

    print(f'异动人员匹配在职表后有{mergedate.shape[0]}条数据')
    # print( f'异动人员匹配在职表后有{mergedate.shape[0]}条数据')
    # 筛选符合大规则的数据
    messageLocList = messageDate.iloc[:, 0].tolist()
    excelDate = mergedate[mergedate['所属公司'].isin(messageLocList)]
    print(f'本次筛选所属公司属于{messageLocList}，的BU的人员')
    # print( f'本次筛选所属公司属于{messageList}，的BU的人员')

    # 课长层以上的，并且人员类型为劳动合同工或裕备生的
    dates1 = excelDate[excelDate['职层'].isin(filterate)]
    datePlus1 = dates1[dates1['人员类别'].isin(type)]
    # 职层为课长层以下的，并且入职时间满一年的，并且 人员类别为：劳动合同工或裕备生的
    current_time = datetime.datetime.now()
    one_year_ago = current_time - datetime.timedelta(days=365)
    time = one_year_ago.strftime("%Y-%m-%d")
    dates2 = excelDate[~excelDate['职层'].isin(filterate)]
    oneyear = dates2[dates2['入职日期'] <= time]
    # datePlus2 = oneyear[oneyear['人员类别'].isin(['劳动合同工', '裕备生'])]
    # 属于劳动合同工、裕备生的并且入职满一年的
    datePlusdate1 = oneyear[oneyear['人员类别'].isin(['劳动合同工', '裕备生'])]
    # 人员类别 属于'退休返聘'人员的都购买
    datePlusdate2 = dates2[dates2['人员类别'].isin(['退休返聘'])]
    # 岗位 属于'顾问','实习生'人员的都购买
    datePlusdate3 = dates2[dates2['岗位'].isin(['顾问', '实习生'])]

    datePusdateAll = pd.concat([datePlusdate1, datePlusdate2, datePlusdate3], axis=0)
    datePlus2 = datePusdateAll.drop_duplicates()  # 去除重复行

    print(datePlus2)
    # 整合两个dateFrame，高管层和普通层的
    lastDate = pd.concat([datePlus1, datePlus2], axis=0)
    print(f'异动人员中符合大规则的有{lastDate.shape[0]}条数据')
    # print( f'异动人员中符合大规则的有{lastDate.shape[0]}条数据')

    # 将再保人员清单的'证件号'列重命名为'证件号码'
    isBuyExcel.rename(columns={'证件号': '证件号码'}, inplace=True)
    # 整合后的，与再保人员进行匹配,两者都存在的数据
    mergeDate = pd.merge(lastDate, isBuyExcel['证件号码'], on='证件号码', how='inner')
    print(f'异动人员中符合大规则的且与在保人员清单匹配后有{mergeDate.shape[0]}条数据')
    # print( f'异动人员中符合大规则的且与在保人员清单匹配后有{mergeDate.shape[0]}条数据')

    # 将于在保人员表比对后的数据保存到Excel中
    day = datetime.datetime.now().strftime('%Y-%m-%d')
    与在保数据筛选后的异动人员 = f'{本地运行文件夹路径}/与在保数据筛选后的异动人员{day}.xlsx'
    mergeDate.to_excel(与在保数据筛选后的异动人员, index=None)

    # 获取保全模板中增加被保险人和减少被保险人sheet页的行数
    insertExceladd = pd.read_excel(保全模板路径, sheet_name='增加被保险人', dtype='str')
    insertExceldel = pd.read_excel(保全模板路径, sheet_name='减少被保险人', dtype='str')

    rowNumberadd = insertExceladd.shape[0] + 1
    rowNumberdel = insertExceldel.shape[0] + 1
    print(f'增加被保险人sheet页中原来有:{rowNumberadd}行数据')
    # print( f'增加被保险人sheet页中原来有:{rowNumberadd}行数据')
    print(f'减少被保险人sheet页中原来有:{rowNumberdel}行数据')
    # print( f'减少被保险人sheet页中原来有:{rowNumberdel}行数据')

    # 模板增加‘减少被保险人’sheet页数据
    离职减员列表 = []
    for index, row in mergeDate.iterrows():
        dateList = ['', '', '', '', '', '', '', '', '', '', '', '', '']
        dateList[0] = str(index + rowNumberdel)
        dateList[1] = row['姓名']
        dateList[2] = row['证件类型']
        dateList[3] = row['证件号码']
        dateList[4] = datetime.datetime.now().strftime('%Y%m%d')

        dateList[10] = '超追溯'
        # 根据工号查找到异动后的所属公司
        numberList = []
        number = row['工号']
        numberList.append(number)
        numberRow = changeexcel[changeexcel['工号'].isin(numberList)]
        dateList[5] = numberRow['所属公司'].item()
        dateList[11] = '异动到' + dateList[5]

        离职减员列表.append(dateList)
    print(f'将要写入到减少被保险人sheet页中的有:{len(离职减员列表)}行数据')
    # print( f'将要写入到减少被保险人sheet页中的有:{len(离职减员列表)}行数据')
    # 用openyxl写入Excel中对应sheet页的数据
    wb = openpyxl.load_workbook(保全模板路径)
    sheet = wb['减少被保险人']
    start_row = rowNumberdel + 1
    for item in 离职减员列表:
        for col in range(1, len(item) + 1):
            sheet.cell(row=start_row, column=col).value = str(item[col - 1])
        start_row += 1
    # 写入完成，暂时不保存

    # 模板增加‘减少被保险人’sheet页数据
    messageDate = pd.read_excel(基础配置文件路径, sheet_name='海外工作地点', dtype=str)
    messageList = messageDate.iloc[:, 0].tolist()
    print(f'所属公司包含{messageList}，的是属于海外员工方案的')
    # print( f'所属公司包含{messageList}，的是属于海外员工方案的')
    在职人员列表 = []
    for index, row in mergeDate.iterrows():
        dateList = ['', '', '', '', '', '', '', '', '', '', '', '', '', '']
        # 工号为空时就退出
        if str(row['工号']) == 'nan':
            break
        # 序号
        dateList[0] = str(index + rowNumberadd)
        # 保障方案
        if str(row['工作地点']) in messageList:
            dateList[1] = '海外员工方案'
        else:
            dateList[1] = '员工方案'
        # 被保险人姓名
        dateList[2] = row['姓名']
        # 证件类型
        dateList[3] = row['证件类型']
        # 证件号
        dateList[4] = str(row['证件号码'])
        # 性别
        if row['性别'] == '男':
            dateList[7] = 'M'
        else:
            dateList[7] = 'F'
        # 出生日期
        dateList[8] = row['出生日期'].replace('-', '')
        # 生效日期
        day = datetime.datetime.now().strftime('%Y%m%d')
        dateList[9] = day
        # 获取新居住地，根据工号去匹配，因为在异动人员表中不存在证件号码
        numberList = []
        number = row['工号']
        numberList.append(number)
        numberRow = changeexcel[changeexcel['工号'].isin(numberList)]
        dateList[10] = numberRow['工号'].item()
        # 投保原因
        dateList[11] = '内部异动'
        # 保险人性质
        dateList[12] = '重新投保'
        dateList[13] = row['所属公司']
        在职人员列表.append(dateList)
    print(f'将要写入到增加被保险人sheet页中的有:{len(离职减员列表)}行数据')
    # print( f'将要写入到增加被保险人sheet页中的有:{len(离职减员列表)}行数据')
    # wb = openpyxl.load_workbook(保全模板路径)
    sheet = wb['增加被保险人']
    start_row = rowNumberadd + 1
    for item in 在职人员列表:
        for col in range(1, len(item) + 1):
            sheet.cell(row=start_row, column=col).value = str(item[col - 1])
        start_row += 1

    wb.save(保全模板路径)
'''

# MessageTow = '本次流程运行共选择{}个BU的的人员,下载异动人员数据:{}条数据,过滤出本月的且非同一地区BU的人员{}条,其中属于课长层以上的{}条,属于员工层的{}条,比对裕心在保名单{}条数据,得出新增异动被保人员{}条,得出减少异动被保人员{}条'.format(
#     len(messageLocList), changeExcelDate.shape[0], changeexcel.shape[0], datePlus1.shape[0], datePlus2.shape[0],
#     mergeDate.shape[0], mergeDate.shape[0], mergeDate.shape[0])


# insertChangedate()


import shutil


# 源文件路径
# src = "/path/to/source/file.txt"
#
# # 目标文件路径（包括新的文件名）
# dst = "/path/to/destination/new_file.txt"

# 使用shutil.copy2复制并重命名文件
# shutil.copy2(src, dst)
# 拆分各对应BU的数据,并发送给邮件
def chaifenBU():
    基础配置文件路径 = '../文件/基础信息配置文件.xlsx'
    保全模板路径 = '../文件/申请保全模板.xlsx'
    空白保全模板路径 = '../文件/空白申请保全模板.xlsx'
    本地运行文件 = '../文件/拆分后'
    在保人员清单文件 = '../文件/裕心在保人员清单.xlsx'

    pd.set_option('display.max_columns', None)
    warnings.filterwarnings('ignore')
    heardmap = {'被授权人': 0, '授权人办公地址': 1, '个人邮箱': 2, '授权邮箱': 3, '在缴BU': 4,
                '关联机构全称': 5}  # 定义一个关于excel表头的列号的map
    readDate = pd.read_excel(基础配置文件路径, sheet_name='缴裕心险的BU', dtype=str)
    readDate.fillna('NAN', inplace=True)
    print(readDate)
    # 进行分组，并将‘在缴BU’这一列的数据合并并用逗号分隔开
    # result = readDate.groupby(['被授权人'])
    head = {'被授权人': 'first', '授权人办公地址': 'first', '个人邮箱': 'first', '授权邮箱': 'first'}
    grouped = readDate.groupby('被授权人')
    first_bc = grouped.agg(head)
    # 整合在缴BU和关联机构全称字段，整理成列表形式，用逗号分隔开
    merged_d = grouped['在缴BU'].apply(lambda x: ','.join(map(str, x)))
    merged_E = grouped['关联机构全称'].apply(lambda x: ','.join(map(str, x)))
    result = pd.concat([first_bc, merged_d], axis=1)
    result = pd.concat([result, merged_E], axis=1)
    print(result)
    # readDate[head]=readDate.groupby('被授权人')[head].transform('first')
    # readDate[['被授权人','在缴BU']]=readDate.groupby(['被授权人'])['在缴BU'].agg(lambda x: ','.join(map(str, x))).reset_index()
    # print(readDate)
    # result=readDate
    # result = readDate.groupby(['被授权人','授权人办公地址','个人邮箱'])['在缴BU'].agg(lambda x: ','.join(map(str, x))).reset_index()
    resultList = result.values.tolist()
    print(resultList)
    # 将同属于一个人管理的BU整理到一个map中{被授权人：['被授权人','授权人办公地址','个人邮箱','授权邮箱',[在缴BU]]}
    resultMap = {}
    for item in resultList:
        item[heardmap['在缴BU']] = item[heardmap['在缴BU']].split(',')
        item[heardmap['关联机构全称']] = item[heardmap['关联机构全称']].split(',')
        resultMap[item[heardmap['被授权人']]] = item
    print(resultMap)
    # 读取'增加被保险人','减少被保险人'sheet页的数据,
    readAddDate = pd.read_excel(保全模板路径, sheet_name='增加被保险人', dtype=str, header=5)
    readDelateDate = pd.read_excel(保全模板路径, sheet_name='减少被保险人', dtype=str, header=6)
    readDelateDate.rename(columns={'Unnamed: 11': '备注'}, inplace=True)
    print(readAddDate)
    print(readDelateDate)
    # 读取裕心在保人员清单文件中的数据，用于过滤关联机构全称不属于当前人员管理的机构
    readInsuranceDate = pd.read_excel(在保人员清单文件, dtype=str)

    for key, value in resultMap.items():
        # 因为会存在上上个月的数据，但是这个人又已经异动到其他BU去了，名字又在在保人员清单文件中。
        # 所以还需要一个过滤条件：根据这个人负责的机构名称来判断这个异动人员是否还在管理的机构中，在的话就属于异动，不在的话就表示已经上个月异动到其他地区了
        # 筛选属于key这个人管理的的,增加被保险人中属于这个人管理的BU
        addDate = readAddDate[readAddDate['所属公司'].isin(value[heardmap['在缴BU']])]
        addDate.fillna('', inplace=True)
        # print(addDate)
        NewInsurance = addDate[addDate['投保原因'].isin(['新入职'])]  # 筛选新入职的
        movementDate = addDate[addDate['投保原因'].isin(['内部异动'])]  # 筛选内部异动的
        addNumberList = movementDate['证件号'].values.tolist()  # 将内部异动的数据证件号转为List
        addNumberDate = readInsuranceDate[readInsuranceDate['证件号'].isin(addNumberList)]  # 筛选在保人员清单文件中的异动人员
        addinstitution = addNumberDate[
            addNumberDate['关联投保人'].isin(value[heardmap['关联机构全称']])]  # 晒选出关联投保人数据当前人员管理的
        NumberList = addinstitution['证件号'].values.tolist()  # 筛选结果的证件号转换为List
        resultAddDate = movementDate[movementDate['证件号'].isin(NumberList)]  # 筛选内部异动中属于这些证件号的人
        concatAddDate = pd.concat([NewInsurance, resultAddDate], axis=0)  # 拼接新入职和内部异动的人员
        addDateList = concatAddDate.values.tolist()
        # print(addDateList)

        # 减少被保险人中属于这个人管理的BU
        delateDate = readDelateDate[readDelateDate['所属公司'].isin(value[heardmap['在缴BU']])]
        delateDate.fillna('', inplace=True)
        # print(delateDate)
        NewDepart = delateDate[delateDate['备注'].isin(['离职'])]  # 筛选新离职的
        movementDepartDate = delateDate[~delateDate['备注'].isin(['离职'])]  # 筛选内部异动的
        delNumberList = movementDepartDate['证件号'].values.tolist()  # 将内部异动的数据证件号转为List
        delNumberDate = readInsuranceDate[readInsuranceDate['证件号'].isin(delNumberList)]  # 筛选在保人员清单文件中的异动人员
        delinstitution = delNumberDate[
            delNumberDate['关联投保人'].isin(value[heardmap['关联机构全称']])]  # 晒选出关联投保人数据当前人员管理的
        delNumberList = delinstitution['证件号'].values.tolist()  # 筛选结果的证件号转换为List
        resultdelDate = movementDepartDate[movementDepartDate['证件号'].isin(delNumberList)]  # 筛选内部异动中属于这些证件号的人
        concatDelDate = pd.concat([NewDepart, resultdelDate], axis=0)  # 拼接新离职和内部异动的人员
        delateDateList = concatDelDate.values.tolist()

        if (len(addDateList) == 0 and len(delateDateList) == 0):
            print(f'没有属于{key}管理的{value}中的需要新增或者减少的人员')
            continue
        saveFile = 本地运行文件 + '\\' + key + '-裕心申请保全数据.xlsx'
        shutil.copy2(空白保全模板路径, saveFile)
        wb = openpyxl.load_workbook(saveFile)
        sheet = wb['增加被保险人']
        start_row = 10
        number1 = 1
        for item in addDateList:
            # 将序号重新排序
            sheet.cell(row=start_row, column=1).value = str(number1)
            number1 += 1
            for col in range(2, len(item) + 1):
                sheet.cell(row=start_row, column=col).value = str(item[col - 1])
            sheet.cell(row=start_row,
                       column=44).value = f'=IF(TODAY()-CONCATENATE(LEFT(J{start_row},4),"/",MID(J{start_row},5,2),"/",RIGHT(J{start_row},2))>=$AR$8,"超追溯","正常")'

            start_row += 1

        sheet1 = wb['减少被保险人']
        start_row1 = 10
        number2 = 1
        for item in delateDateList:

            sheet1.cell(row=start_row1, column=1).value = str(number2)
            number2 += 1
            for col in range(2, len(item) + 1):
                sheet1.cell(row=start_row1, column=col).value = str(item[col - 1])
            sheet1.cell(row=start_row1,
                        column=11).value = f'=IF(TODAY()-CONCATENATE(LEFT(E{start_row1},4),"/",MID(E{start_row1},5,2),"/",RIGHT(E{start_row1},2))>=$K$8,"超追溯","正常")'
            start_row1 += 1
        wb.save(saveFile)
        # if (key =='NAN'):
        #     收件人邮箱号=globalConfig['收件人邮箱号']
        # else:
        #     收件人邮箱号=value[heardmap['个人邮箱']]
        # #引用发送邮件子流程
        # self.task_VI7HF()


chaifenBU()


def somemMssage():
    本地运行文件夹路径 = '../文件'
    在保人员清单文件 = '../文件/裕心在保人员清单.xlsx'
    在职人员名单文件 = '../文件/下载在职人员报表2024-02-22.xlsx'
    离职人员名单文件 = '../文件/离职人员报表2024-01-25.xlsx'
    异动人员名单文件 = '../文件/异动人员报表2024-01-29.xlsx'
    特殊人员清单文件 = '../文件/裕心特殊人员清单（一般不需修改）.xlsx'
